unit TrigRest;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ComCtrls, Db, FMTBcd, SqlExpr;

type
  TTrig = class(TDataModule)
    NewTrig: TSQLDataSet;
    OldTrig: TSQLDataSet;
    NewCheck: TSQLDataSet;
  private
    { Private declarations }
    function GetTrigName: String;
    function GetTableName: String;
    function GetTrigType: String;
  private
    { Private declarations }
    property TrigName : String read GetTrigName;
    property TableName : String read GetTableName;
    property TrigType : String read GetTrigType;
    function TriggerIsUD : Boolean;
    function TriggerIsCheck : Boolean;
    procedure CreateSQLStatement;
    procedure CreateTrigger;
    procedure AlterTrigger;
  public
    { Public declarations }
    procedure CheckTrigger;
  end;

var
  Trig: TTrig;

procedure CheckTriggers(ProgBar : TProgressBar);

implementation

uses Data, ReportForm;

{$R *.DFM}

procedure CheckTriggers;
begin
  ProgBar.Position:=0;
  // how many triggers are there ?
  Trig.NewTrig.CommandText:=
    'SELECT count(RDB$Trigger_NAME) as ccc FROM RDB$Triggers';
  Trig.NewTrig.Open;
  ProgBar.Max:=Trig.NewTrig.fieldByName('ccc').AsInteger;
  Trig.NewTrig.Close;

  Trig.NewTrig.CommandText:=
    'SELECT * FROM RDB$TRIGGERS';

  Trig.NewTrig.Open;
  Trig.OldTrig.Open;
  Trig.NewCheck.Open;
  Trig.NewTrig.First;
  While not Trig.NewTrig.Eof do
  begin
    Application.ProcessMessages;
    ProgBar.Position:=ProgBar.Position+1;
    Trig.CheckTrigger;
    Trig.NewTrig.Next;
  end;
  Trig.NewCheck.Close;
  Trig.OldTrig.Close;
  Trig.NewTrig.Close;
end;

{ TTrig }

function TTrig.GetTrigName: String;
begin
  Result:=Trim(NewTrig.FieldByName('RDB$TRIGGER_NAME').AsString);
end;

function TTrig.GetTableName: String;
begin
  Result:=Trim(NewTrig.FieldByName('RDB$RELATION_NAME').AsString);
end;

function TTrig.GetTrigType: String;
begin
  case NewTrig.FieldByName('RDB$TRIGGER_TYPE').AsInteger of
    1 : Result:='ACTIVE BEFORE INSERT ';
    2 : Result:='ACTIVE AFTER INSERT ';
    3 : Result:='ACTIVE BEFORE UPDATE ';
    4 : Result:='ACTIVE AFTER UPDATE ';
    5 : Result:='ACTIVE BEFORE DELETE ';
    6 : Result:='ACTIVE AFTER DELETE ';
  end;
  Result:=Result+'POSITION '+NewTrig.FieldByName('RDB$TRIGGER_SEQUENCE').AsString;
end;

function TTrig.TriggerIsCheck: Boolean;
begin
  Result:=NewCheck.Locate('RDB$TRIGGER_NAME',TrigName,[]);
end;

function TTrig.TriggerIsUD: Boolean;
begin
  Result:=NewTrig.FieldByName('RDB$SYSTEM_FLAG').AsInteger<>1;
end;

procedure TTrig.CheckTrigger;
var TrigSrcOk,
    TrigExist : Boolean;
begin
  // is this trigger or check constraint ?
  if TriggerIsUD and not TriggerIsCheck then
  begin
    TrigExist:=True;
    TrigSrcOk:=True;
    // does trigger exist ?
    OldTrig.Close;
    OldTrig.ParamByName('TNAME').AsString:=TrigName;
    OldTrig.Open;
    if Trim(OldTrig.FieldByName('RDB$TRIGGER_NAME').AsString)=TrigName then
    begin
      // now I have to check trigger source
      TrigSrcOk:=
        (OldTrig.FieldByName('RDB$TRIGGER_SOURCE').AsString=
         NewTrig.FieldByName('RDB$TRIGGER_SOURCE').AsString) and
        (OldTrig.FieldByName('RDB$TRIGGER_SEQUENCE').AsInteger=
         NewTrig.FieldByName('RDB$TRIGGER_SEQUENCE').AsInteger) and
        (OldTrig.FieldByName('RDB$TRIGGER_TYPE').AsInteger=
         NewTrig.FieldByName('RDB$TRIGGER_TYPE').AsInteger);
    end
      else TrigExist:=False;

    if not TrigExist then CreateTrigger else
      if not TrigSrcOk then AlterTrigger;
  end;
end;

procedure TTrig.CreateSQLStatement;
begin
  DM.SQL.Add(TrigType);
  DM.SQL.Add(NewTrig.FieldByName('RDB$TRIGGER_SOURCE').AsString);
end;

procedure TTrig.CreateTrigger;
begin
  DM.SQL.Clear;
  DM.SQL.Add('CREATE TRIGGER '+TrigName+' FOR '+TableName);
  CreateSQLStatement;

  if DM.AutoRepair then
  begin
    try
      DM.ExecRunSQL;
      if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'not exist');
    except
      if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'error');
  end;
  end else
  begin
    DM.S.AddStrings(DM.SQL);
    if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'not exist');
  end;
end;

procedure TTrig.AlterTrigger;
begin
  DM.SQL.Clear;
  DM.SQL.Add('ALTER TRIGGER '+TrigName);
  CreateSQLStatement;

  if DM.AutoRepair then
  begin
    try
      DM.ExecRunSQL;
      if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'different');
    except
      if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'error');
  end;
  end else
  begin
    DM.S.AddStrings(DM.SQL);
    if DM.ShowReport then Rep.AddToReport('TRIGGER.',Trim(TrigName),'different');
  end;
end;

end.
